Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# CURSOR Expressions

A `CURSOR` expression returns a nested cursor. This form of expression is equivalent to the PL/SQL `REF` `CURSOR` and can be passed as a `REF` `CURSOR` argument to a function.

Description of the illustration cursor_expression.gif

A nested cursor is implicitly opened when the cursor expression is evaluated. For example, if the cursor expression appears in a select list, a nested cursor will be opened for each row fetched by the query. The nested cursor is closed only when:

- The nested cursor is explicitly closed by the user

- The parent cursor is reexecuted

- The parent cursor is closed

- The parent cursor is cancelled

- An error arises during fetch on one of its parent cursors (it is closed as part of the clean-up)

Restrictions on CURSOR Expressions The following restrictions apply to `CURSOR` expressions:

- If the enclosing statement is not a `SELECT` statement, nested cursors can appear only as `REF` `CURSOR` arguments of a procedure.

- If the enclosing statement is a `SELECT` statement, nested cursors can also appear in the outermost select list of the query specification or in the outermost select list of another nested cursor.

- Nested cursors cannot appear in views.

- You cannot perform `BIND` and `EXECUTE` operations on nested cursors.

Examples The following example shows the use of a `CURSOR` expression in the select list of a query:

    SELECT department_name, CURSOR(SELECT salary, commission_pct
       FROM employees e
       WHERE e.department_id = d.department_id)
       FROM departments d;

The next example shows the use of a `CURSOR` expression as a function argument. The example begins by creating a function in the sample `OE` schema that can accept the `REF` `CURSOR` argument. (The PL/SQL function body is shown in italics.)

    CREATE FUNCTION f(cur SYS_REFCURSOR, mgr_hiredate DATE)
       RETURN NUMBER IS
       emp_hiredate DATE;
       before number :=0;
       after number:=0;
    begin
      loop
        fetch cur into emp_hiredate;
        exit when cur%NOTFOUND;
        if emp_hiredate > mgr_hiredate then
          after:=after+1;
        else
          before:=before+1;
        end if;
      end loop;
      close cur;
      if before > after then
        return 1;
      else
        return 0;
      end if;
    end;
    /

The function accepts a cursor and a date. The function expects the cursor to be a query returning a set of dates. The following query uses the function to find those managers in the sample `employees` table, most of whose employees were hired before the manager.

    SELECT e1.last_name FROM employees e1
       WHERE f(
       CURSOR(SELECT e2.hire_date FROM employees e2
       WHERE e1.employee_id = e2.manager_id),
       e1.hire_date) = 1;

    LAST_NAME
    -------------------------
    De Haan
    Mourgos
    Cambrault
    Zlotkey
    Higgens